<?php


namespace app\admin\controller;


use app\common\controller\Backend;
use app\common\model\Level as LevelModel;
use think\Request;
use think\Db;

class Import extends Backend
{

    /**
     * @var \app\common\model\Category
     */
    protected $model = null;
    protected $categorylist = [];


    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('app\common\model\Level');

        $this->categorylist =   $this->model->order("l_sort desc")->select();
        $typeList = LevelModel::getTypeList();
        $this->assignconfig('typeList', $typeList);
    }

    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            $search = $this->request->request("search");
            $type = $this->request->request("type");

            //构造父类select列表选项数据
            $list = [];


            foreach ($this->categorylist as $k => $v) {
                if ($search) {
                    if ($v['type'] == $type && stripos($v['name'], $search) !== false || stripos($v['nickname'], $search) !== false) {
                        if ($type == "all" || $type == null) {
                            $list = $this->categorylist;
                        } else {
                            $list[] = $v;
                        }
                    }
                } else {
                    if ($type == "all" || $type == null) {
                        $list = $this->categorylist;
                    } elseif ($v['type'] == $type) {
                        $list[] = $v;
                    }
                }
            }

            $total = count($list);
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    public function import()
    {
        if (empty($this->request->post('file'))) {
            $this->error('请上传文件');
        }
        vendor('phpexcel.PHPExcel');
        $filePath = $this->request->post('file');
        $arr = explode('.', $filePath);
        if ($arr[count($arr) - 1] != 'xlsx' && $arr[count($arr) - 1] != 'xls') {
            $this->error('文件格式不正确');
        }
        $objreder = \PHPExcel_IOFactory::load(ROOT_PATH . DS . 'public' . DS . $filePath);
        $sheet = $objreder->getActiveSheet();
        $merge = $sheet->getMergeCells();//获取合并的单元格数据
        $varchar = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
            "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"];
        //取列合并数据
        $mergeColumn = [];//列合并
        $mergeRow = [];//行合并
        if ($merge) {
            foreach ($merge as $cells) {
                $str = explode(':', $cells);
                if (count($str) >= 2) {
                    if (substr($str[0], 0, 1) == substr($str[1], 0, 1)) {//代表列合并
                        $mergeColumn[] = $cells;
                    } else {
                        $mergeRow[] = $cells;
                    }
                }
            }
        }
        $sheetCount = $objreder->getSheetCount();//获取excel文件里有多少个sheet
        $unit = '';//填表单位
        $unit_id = 0;
        $assignment_id = 0;
        $proof = '';
        //这里写死只读sheet1
        for ($i = 0; $i < 1; $i++) {
            $data = $objreder->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入到数组中
        }
        Db::startTrans();
        try {
            for ($j = 4; $j < count($data); $j++) {
                if (is_string($data[$j][0]) && strlen(trim($data[$j][0])) >= 6) {//粗略判断是否为填报单位
                    $unit = preg_replace('# #', '', trim($data[$j][0]));//获取新单位
                    //判断新单位是否存在,不存在存入数据库单位表
                    $units = Db::table('fa_ministries')->where('m_name', 'like', "%" . $unit . "%")->find();
                    if ($units) {
                        $unit_id = $units['id'];
                    } else {
                        $unit_id = Db::table('fa_ministries')->insertGetId(['m_name' => $unit, 'ctime' => time()]);
                    }
                } else {
                    //preg_replace('# #','',$goodid)
                    if (!$unit) {
                        continue;//新单位不存在跳过本次循环
                    }
                    $details = Db::table('fa_project')->where('title', preg_replace('# #', '', $data[$j][3]))->where('title', 'neq', '')->find();
                    if ($details) {
//                        continue;//任务编号存在则跳过本次循环
                        $isDetails = true;
                    } else {
                        $isDetails = false;
                    }
                    if ($data[$j][1]) {
                        $assignment = preg_replace('# #', '', $data[$j][1]);
                        //判断任务是否存在,不存在存入数据库任务表
                        $assignments = Db::table('fa_category')->where('pid', $unit_id)->where('name', $assignment)->find();
                        if ($assignments) {
                            $assignment_id = $assignments['id'];
                        } else {
                            $assignment_id = Db::table('fa_category')->insertGetId(['pid' => $unit_id, 'name' => $assignment, 'createtime' => time()]);
                        }
                    } else {
                        //判断是否为合并单元格，预留
                        $isRange = false;
                        foreach ($merge as $cells) {
                            $cell = $sheet->getCell($varchar[1] . ($j + 1));
                            if ($cell->isInRange($cells)) {
                                $isRange = true;
                            }
                        }
                        if (!$isRange) {
                            $assignment_id = 0;
                        }
                    }
                    //责任主体表
                    $subject = preg_replace('# #', '', $data[$j][5]);
                    if ($subject) {
                        $subjects = Db::table('fa_subject')->where('subject', $subject)->find();
                        if ($subjects) {
                            $subject_id = $subjects['id'];
                        } else {
                            $subject_id = Db::table('fa_subject')->insertGetId(['subject' => $subject, 'ctime' => time()]);
                        }
                    } else {
                        $subject_id = 0;
                    }


                    if ($data[$j][2]) {
                        $proof = preg_replace('# #', '', $data[$j][2]);
                    }
                    //payment资金来源表
                    $payment['unit_id'] = $unit_id;
                    $payment['a_id'] = $assignment_id;
                    $payment['payment'] = preg_replace('# #', '', $data[$j][20]);
                    $payment['expense'] = preg_replace('# #', '', $data[$j][21]);
                    $payment['ratio'] = preg_replace('# #', '', $data[$j][22]);
                    $payment['other'] = preg_replace('# #', '', $data[$j][23]);
                    $payment_id = Db::table('fa_payment')->insertGetId($payment);
                    //任务详情表
                    $detail['number'] = $data[$j][0];
                    $detail['unit_id'] = $unit_id;
                    $detail['a_id'] = $assignment_id;
                    $detail['proof'] = $proof;
                    $detail['title'] = preg_replace('# #', '', $data[$j][3]);;
                    $detail['survey'] = preg_replace('# #', '', $data[$j][4]);
                    $detail['subject'] = $subject_id;
                    $detail['innovation'] = preg_replace('# #', '', $data[$j][6]);
                    $detail['ministries'] = preg_replace('# #', '', $data[$j][7]);
                    $detail['municipal'] = preg_replace('# #', '', $data[$j][8]);
                    $detail['contractor'] = preg_replace('# #', '', $data[$j][9]);
                    $detail['track'] = preg_replace('# #', '', $data[$j][10]);
                    $detail['investment'] = preg_replace('# #', '', $data[$j][11]);
                    $detail['assessment'] = preg_replace('# #', '', $data[$j][12]);
                    $detail['target_20'] = preg_replace('# #', '', $data[$j][13]);

//                    for ($i=1;$i<6;$i++){
//                        if(preg_replace('# #', '',$data[$j][13+$i]) != ''){
//                            if(preg_replace('# #', '',$data[$j][12+$i]) == ''){
//                                Db::rollback();
//                                $this->error('数据不合法');
//                            }
//                        }
//                    }
                    $detail['target_21'] = preg_replace('# #', '', $data[$j][14]);
                    $detail['target_22'] = preg_replace('# #', '', $data[$j][15]);
                    $detail['target_23'] = preg_replace('# #', '', $data[$j][16]);
                    $detail['target_24'] = preg_replace('# #', '', $data[$j][17]);
                    $detail['target_25'] = preg_replace('# #', '', $data[$j][18]);
                    $res = true;
                    if ($detail['target_20'] == '') {
                        $res = false;
                    }
                    $count = 1;

                    if ($detail['target_21'] != '') {
                        $count++;
                    }
                    if ($detail['target_22'] != '') {
                        if ($detail['target_21'] == '') {
                            $res = false;
                        } else {
                            $count++;
                        }
                    }
                    if ($detail['target_23'] != '') {
                        if ($detail['target_22'] == '') {
                            $res = false;
                        } else {
                            $count++;
                        }
                    }
                    if ($detail['target_24'] != '') {
                        if ($detail['target_23'] == '') {
                            $res = false;
                        } else {
                            $count++;
                        }
                    }
                    if ($detail['target_25'] != '') {
                        if ($detail['target_24'] == '') {
                            $res = false;
                        } else {
                            $count++;
                        }
                    }
                    if (!$res) {
                        Db::rollback();
                        $this->error('数据不合法');
                        break;
                    }
                    $detail['file'] = preg_replace('# #', '', $data[$j][19]);
                    $detail['remark'] = preg_replace('# #', '', $data[$j][24]);
                    $detail['p_id'] = $payment_id;
                    $detail['count'] = $count * 6;
                    if ($isDetails) {
                        Db::table('fa_project')->where('id', $details['id'])->update($detail);
                    } else {
                        Db::table('fa_project')->insert($detail);
                    }

                }
            }
            Db::commit();
        } catch (\Exception $e) {
            Db::rollback();
        }
    }
}